﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utils;
using DataHelper;

namespace DAL
{
    public class EmpLeaveDao
    {

        #region 离职信息
      BuiltSQL builtSQL=  new BuiltSQL();
      public bool Insert(string tableName, SortedDictionary<string, string> sdList, SortedDictionary<string, string> hList)
        {
            //
            StringBuilder builder = new StringBuilder();
            builder.Append("Update Admin_Emp_ReIn set Statu='N' where EmpCode ='" + hList["EmpCode"] + "' and Statu<>'N' ;Update Admin_Employee Set StateID=" + hList["StateID"] + ",LeaveTime='" + hList["LeaveTime"] + "',AskForLeave='" + hList["AskForLeave"] + "',SafetyAmount=0  Where EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Update Admin_Insurance Set Statu='N',EndTime='" + hList["LeaveTime"] + "',StatuTime='" + System.DateTime.Now.ToString() + "' Where Statu<>'N' and EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Update Sys_Account Set [State]='N',Resion='离职' Where EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Insert into Admin_Emp_Leave (EmpCode,EmpName,DeptName,DutyName,HisStateID,LeaveTime,LeaveType,DayNum,IsProcedure,Account,Memo,IsSalary) values ");
            builder.Append("('" + sdList["EmpCode"] + "','" + sdList["EmpName"] + "','" + sdList["DeptName"] + "','" + sdList["DutyName"] + "'," + sdList["HisStateID"] + ",'" + sdList["LeaveTime"] + "','" + sdList["LeaveType"] + "'," + sdList["DayNum"] + ",'" + sdList["IsProcedure"] + "','" + sdList["Account"] + "','" + sdList["Memo"] + "','" + sdList["IsSalary"] + "');");
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
        }
        //根据工号 更改状态   返聘时使用
      public bool UpdateStatuToN(string empCode)
        {
            return SQLHelper.ExecuteSql("update Admin_Emp_Leave set Statu='N' where Statu='Y' and EmpCode='" + empCode + "'");
        }
        public bool Update(string tableName, SortedDictionary<string, string> sdList)
        {
            string stateID=string.Empty;
            if (sdList["LeaveType"] == "请长假")//雇员表中同样映射为请长假   
                stateID = "4";
            else
                stateID = "3";
            StringBuilder builder = new StringBuilder();
            builder.Append("Update Admin_Employee Set StateID=" + stateID + ",LeaveTime='" + sdList["LeaveTime"] + "',AskForLeave='" + sdList["Memo"] + "',SafetyAmount=0  Where EmpCode='"+sdList["EmpCode"].Trim()+"';");
            builder.Append("Update Admin_Insurance Set Statu='N',EndTime='" + sdList["LeaveTime"] + "',StatuTime='" + System.DateTime.Now.ToString() + "' Where Statu<>'N' and EmpCode='" + sdList["EmpCode"] + "';");
            builder.Append("Update Sys_Account Set [State]='N',Resion='离职' Where EmpCode='" + sdList["EmpCode"] + "';Update Wage_EmpItem Set Statu='N'  Where EmpCode='" + sdList["EmpCode"] + "'; Update Wage_EmpSets Set Statu='N'  Where EmpCode='" + sdList["EmpCode"] + "';");
            builder.Append("Update Admin_Emp_Leave set ");
            builder.Append("EmpCode='" + sdList["EmpCode"] + "',EmpName='" + sdList["EmpName"] + "',DeptName='" + sdList["DeptName"] + "',DutyName='" + sdList["DutyName"] + "',LeaveTime='" + sdList["LeaveTime"] + "',LeaveType='" + sdList["LeaveType"] + "',DayNum=" + sdList["DayNum"] + ",IsProcedure='" + sdList["IsProcedure"] + "',Account='" + sdList["Account"] + "',Memo='" + sdList["Memo"] + "',IsSalary='" + sdList["IsSalary"] + "'");
            builder.Append(" Where LeaveID=" + sdList["LeaveID"]);
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
           // return builtSQL.UpdateTable(tableName, sdList);
        }
        public bool DeleteOneInfo(string leaveID, SortedDictionary<string, string> hList)
        {
            StringBuilder builder = new StringBuilder();
            //SafetyAmount=0 保险状态不修改
            builder.Append("Update Admin_Employee Set StateID=" + hList["StateID"] + ",LeaveTime=null  Where EmpCode='" + hList["EmpCode"].Trim() + "';");
            builder.Append("Update Admin_Insurance Set Statu='Y',EndTime=null,StatuTime='" + System.DateTime.Now.ToString() + "' Where Statu='N' and EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Update Sys_Account Set [State]='Y',Resion='删除离职' Where EmpCode='" + hList["EmpCode"] + "';Update Wage_EmpItem Set Statu='Y'  Where EmpCode='" + hList["EmpCode"] + "'; Update Wage_EmpSets Set Statu='Y'  Where EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Delete from Admin_Emp_Leave Where LeaveID=" + leaveID);
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
        }
        public bool DeleteOneInfo(string leaveID)
        {
            StringBuilder builder = new StringBuilder();
                       builder.Append("Delete from Admin_Emp_Leave Where LeaveID=" + leaveID);
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
        }
        /// <summary>
        /// 获取离职信息一条记录的全部数据
        /// </summary>
        /// <param name="empCode"></param>
        /// <returns></returns>
        public DataTable GetOneInfo(string leaveID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select * from View_Leave where LeaveID=" + leaveID);
            return dt;
        }
        /// <summary>
        /// 根据工号获取离职信息
        /// </summary>
        /// <param name="empCode"></param>
        /// <param name="leaveTime">在某一个时间段之内用</param>
        /// <returns></returns>
        public DataTable GetOneInfo(string empCode, string statu)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt("select * from View_Leave where EmpCode='" + empCode + "' and Statu='"+statu+"'");
            return dt;
        }
        public string GetSQL(string serWord, string bTime, string eTime, string deptID, string leaveType, string leaveState)
        {
            StringBuilder strSQL = new StringBuilder("Select * From View_Leave Where 1=1");
           
            if (!string.IsNullOrWhiteSpace(serWord))
                strSQL.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(leaveState))
                {
                    if (leaveState != "ALL")
                        strSQL.Append(" And Statu='" + leaveState + "'");
                }
                if (leaveType!="0")
                    strSQL.Append("and LeaveType='" + leaveType + "'");
                if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0")
                    strSQL.Append("and DeptID=" + Int32.Parse(deptID));
                if (!string.IsNullOrWhiteSpace(bTime) && RegexRule.IsDate(bTime))
                    strSQL.Append(" And LeaveTime>='" + bTime + "'");
                if (!string.IsNullOrWhiteSpace(eTime) && RegexRule.IsDate(eTime))
                    strSQL.Append(" And LeaveTime<='" + eTime + "'");
            }

            strSQL.Append(" order by DeptName ASC,EmpCode ASC");
            return strSQL.ToString();
        }
        //离职信息列表
        public DataTable GetLists(string serWord, string bTime, string eTime, string deptID, string leaveType, string leaveState)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(GetSQL(serWord, bTime, eTime, deptID, leaveType, leaveState));
            return dt;
        }
        //导出离职信息表
        public DataTable GetOutLists(string serWord, string bTime, string eTime, string deptID, string leaveType, string leaveState)
        {
            string leaveFields = XmlUtility.ReadFiled("Admin_Emp_Leave");
            StringBuilder strSQL = new StringBuilder("Select " + leaveFields + " From View_Leave Where 1=1");
            
            if (!string.IsNullOrWhiteSpace(serWord))
                strSQL.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(leaveState))
                {
                    if (leaveState != "ALL")
                        strSQL.Append(" And Statu='" + leaveState + "'");
                }
                if (leaveType != "0")
                    strSQL.Append("and LeaveType='" + leaveType + "'");
                if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0")
                    strSQL.Append("and DeptID=" + Int32.Parse(deptID));
                if (!string.IsNullOrWhiteSpace(bTime) && RegexRule.IsDate(bTime))
                    strSQL.Append(" And LeaveTime>='" + bTime + "'");
                if (!string.IsNullOrWhiteSpace(eTime) && RegexRule.IsDate(eTime))
                    strSQL.Append(" And LeaveTime<='" + eTime + "'");
            }

            strSQL.Append(" order by DeptName ASC,EmpCode ASC");
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
            return dt;
        }
        #endregion
    }
}
